/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseLayer;

import java.lang.String;
import java.lang.StringBuffer;
import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.NamingException;

import userBean.*;

/**
 *
 * @author Rigsby Chi
 */
public class CustomerUpdate {

    public static void main(String arg[]) {

//        UserIdentity me = UserLookUp.getUserIdentity("52584035", "52584035");
//        System.out.println(me.getClass());
//        CustomerUpdate.userLogin("52584035");
//        me = UserLookUp.getUserIdentity("52584035", "52584035");
//        System.out.println(me.getClass());
//        CustomerUpdate.userLogout("52584035");
//        me = UserLookUp.getUserIdentity("52584035", "52584035");
//        System.out.println(me.getClass());
//
//        int seat[] = {11, 12, 13};
//        CustomerUpdate.purchaseTicket("52584035", seat, "2013-08-01 13:10:11", 100, 10, "Movie1", "Movie1+2");
//        UserLookUp.main(null);
//        int seat2[] = {14, 15, 16};
//        CustomerUpdate.purchaseTicket("52584035", seat2, "2013-08-01 13:10:11", 100, 0, "Movie1", "Movie1+3");
//        CustomerUpdate.timeOutPurchase("52584035+5");
    }

    public static void userLogin(String loginName) {
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("UPDATE LoginUser SET ISLOGIN=1 WHERE LOGINNAME=?");
            pstmt.setString(1, loginName);

            pstmt.executeUpdate();

            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public static void userLogout(String loginName) {
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("UPDATE LoginUser SET ISLOGIN=0 WHERE LOGINNAME=?");
            pstmt.setString(1, loginName);

            pstmt.executeUpdate();

            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public static String purchaseTicket(String loginName, int seatNo[], String buyTime, int money, int point, String movieName, String slotID) {
        String newRecordID = "";
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            String seatString = "";
            for (int i = 0; i < seatNo.length; i++) {
                if(seatNo[i]/10==0){
                    seatString += "00";
                } else if(seatNo[i]/100==0){
                    seatString += "0";
                }
                seatString += seatNo[i];
            }

            PreparedStatement pstmt = con.prepareStatement("select count(*) from buyrecord where loginname=?");
            pstmt.setString(1, loginName);

            ResultSet rs = pstmt.executeQuery();

            int recordID = 0;

            if (rs != null && rs.next()) {
                recordID = rs.getInt(1);
            }

            recordID++;
            newRecordID = loginName + "-" + recordID;

            pstmt = con.prepareStatement("insert into BuyRecord values (?, ?, ?, ?, ?, ?, ?, ?, 0)");
            pstmt.setString(1, newRecordID);
            pstmt.setString(2, loginName);
            pstmt.setString(3, seatString);
            pstmt.setString(4, buyTime);
            pstmt.setInt(5, money);
            pstmt.setInt(6, point);
            pstmt.setString(7, movieName);
            pstmt.setString(8, slotID);

            pstmt.executeUpdate();

            if (point != 0) {
                pstmt = con.prepareStatement("UPDATE LoginUser SET POINTS=POINTS-? WHERE LOGINNAME=?");
                pstmt.setInt(1, point);
                pstmt.setString(2, loginName);
                pstmt.executeUpdate();
            } else {
                pstmt = con.prepareStatement("UPDATE LoginUser SET POINTS=POINTS+? WHERE LOGINNAME=?");
                pstmt.setInt(1, money / 10);
                pstmt.setString(2, loginName);
                pstmt.executeUpdate();
            }

            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return newRecordID;
    }

    public static void requestRefund(String recordID) {
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("UPDATE buyrecord SET RECORDSTATE=3 WHERE RECORDID=?");
            pstmt.setString(1, recordID);

            pstmt.executeUpdate();

            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public static void payPurchase(String recordID) {
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("UPDATE buyrecord SET RECORDSTATE=1 WHERE RECORDID=?");
            pstmt.setString(1, recordID);

            pstmt.executeUpdate();

            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public static void timeOutPurchase(String recordID) {
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("UPDATE buyrecord SET RECORDSTATE=2 WHERE RECORDID=?");
            pstmt.setString(1, recordID);

            pstmt.executeUpdate();

            pstmt = con.prepareStatement("SELECT loginname, moneyused, pointused from buyrecord where recordid = ?");
            pstmt.setString(1, recordID);
            ResultSet rs = pstmt.executeQuery();

            if (rs != null && rs.next()) {
                if (rs.getInt("pointused") > 0) {
                    pstmt = con.prepareStatement("UPDATE loginuser SET POINTS=POINTS+? where LOGINNAME=?");
                    pstmt.setInt(1, rs.getInt("pointused"));
                } else {
                    pstmt = con.prepareStatement("UPDATE loginuser SET POINTS=POINTS-? where LOGINNAME=?");
                    pstmt.setInt(1, rs.getInt("moneyused")/10);
                }
                pstmt.setString(2, rs.getString("loginname"));
            }
            
            pstmt.executeUpdate();

            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    public static boolean isPurchasePaid(String recordID) {
        boolean result = false;
        try {
            Context initCtx = new InitialContext();
            Context envCtx = (Context)initCtx.lookup("java:comp/env");
            DataSource ds = (DataSource)envCtx.lookup("jdbc/Assignment2");
            Connection con = ds.getConnection();

            PreparedStatement pstmt = con.prepareStatement("Select RECORDSTATE from BuyRecord where RECORDID=?");
            pstmt.setString(1, recordID);

            ResultSet rs = pstmt.executeQuery();

            if (rs != null && rs.next()) {
                if(rs.getInt("RECORDSTATE") == 1){
                    result = true;
                }
            }
            
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close(); //close Statement object
            }
            if (con != null) {
                con.close(); //close Connection object 
            }
        } catch (SQLException s) {
            s.printStackTrace();
        } catch (NamingException e) {
            e.printStackTrace();
        }
        return result;
    }
}
